package com.library.dao;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

public class MySQLLendingDAO implements LendingDAO {
	private Connection conn;
	public MySQLLendingDAO(){};
	@Override
	public boolean insertLending(Lending lending) {	
		conn = MySQLConnection.getConnection();
		boolean res = false;
		if (conn != null){
			String querry = "INSERT INTO lendings (userid,bookitemid,date,status,lendingperiod) VALUES('"
					+ lending.getUserId()
					+ "','"
					+ lending.getBookitemId()
					+ "','"
					+ lending.getDate()
					+ "','"
					+ lending.getStatus() + "','" + lending.getLendingPeriod() + "')";
			conn = MySQLConnection.getConnection();
			try {
				Statement stmt;
				stmt = conn.createStatement();
				stmt.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;
			} finally{
				MySQLConnection.closeConnection(conn);
			}
			
		}
		
		return res;
	}

	@Override
	public boolean removeLending(int id) {
		conn = MySQLConnection.getConnection();
		boolean res = false;
		String querry = "DELETE FROM lendings WHERE (lendingid = '"+id+"')";
		Statement st;
		if (conn != null) {
			try {
				st = conn.createStatement();
				st.execute(querry);
				res = true;
			} catch (SQLException e) {
				res = false;		
			} finally {
				MySQLConnection.closeConnection(conn);
			}
		}		
		return res;
	}

	@Override
	public boolean updateLending(Lending lending) {
		Connection conn = MySQLConnection.getConnection();
		if (conn != null) {
			boolean ok = true;
			String query = "UPDATE `lendings` SET "
					+ "`userid` =" + lending.getUserId() + ","
					+ "`date` ='" + lending.getDate() + "',"
					+ "`status` ='" + lending.getStatus() + "',"
					+ "`bookitemid` =" + lending.getBookitemId() + ","
					+ "`lendingperiod` =" + lending.getLendingPeriod()
					+ " WHERE"
					+ "`lendingid` =" + lending.getLendingId();
			try {
				Statement statement = conn.createStatement();
				statement.executeUpdate(query);
			} catch (SQLException e) {
				ok = false;
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			return ok;
		} else {
			return false;
		}
	}

	@Override
	public Lending findLending(int id) {
		conn = MySQLConnection.getConnection();
		Lending len = null;
		if (conn != null){
			String querry = "SELECT * FROM lendings WHERE userid = '"+id+"'";
			Statement stmt;
			try {
				stmt = conn.createStatement();
				ResultSet result = stmt.executeQuery(querry);
				if (result.next()){
					len = new Lending();
					len.setBookItemId(result.getInt("bookitemid"));
					len.setLendingId(result.getInt("lendingid"));
					len.setUserId(result.getInt("userid"));
					len.setStatus(result.getString("status"));
					len.setDate(result.getDate("date"));
				}
			
			}
				
			 catch (SQLException e) {
				e.printStackTrace();
			} finally{
				MySQLConnection.closeConnection(conn);
			}
			
		}

		return len;
	}
	@Override
	public ArrayList<Lending> getUserLending(int userid, String status) {
		ArrayList<Lending> list = new ArrayList<Lending>();
		String query = "SELECT * FROM lendings WHERE ((userid = "+userid+") AND (status ='"+status+"'))";
		conn = MySQLConnection.getConnection();
		if (conn != null){
			try {
				Statement st = conn.createStatement();
				ResultSet result = st.executeQuery(query);
				while (result.next()){
					Lending len = new Lending();
					len.setBookItemId(result.getInt("bookitemid"));
					len.setLendingId(result.getInt("lendingid"));
					len.setUserId(result.getInt("userid"));
					len.setStatus(result.getString("status"));
					len.setDate(result.getDate("date"));
					list.add(len);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				MySQLConnection.closeConnection(conn);
			}
			
		}
		return list;
	}

}
